package com.cjy.sql;

import oracle.sql.TIMESTAMP;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author cjy
 * @create 2020-05-08
 */
public class Save {
    private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    //    private static final String URL = "jdbc:mysql://127.0.0.1:3306/db10?useUnicode=true&characterEncoding=utf8";
//    private static final String URL = "jdbc:mysql://localhost/db10";
    private static final String URL = "jdbc:mysql://192.168.131.133:3306/db10?&useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private static final String SQL = "SELECT * FROM ";// 数据库操作
//    private static  String sql = "insert into datax_job_result values(?,?,?,?,?,?,?,?,?,?,?)";//数据库操作语句（插入）

    private static String start_time = "";
    private static String end_time = "";
    private static int seconds = 0;
    private static String traffic = "";
    private static String write_speed = "";
    private static int read_record = 0;
    private static int failed_record = 0;

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("can not load jdbc driver", e);
        }
    }


    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("success to mysql" + conn);
        } catch (SQLException e) {
            LOGGER.error("get connection failure", e);
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("close connection failure", e);
            }
        }
    }

    public static void readLog(String filename) {
        File file = new File(filename);
        String path = "";

        StringBuilder result = new
                StringBuilder();
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-8"));//构造一个BufferedReader类来读取文件

            String s = null;
            while ((s = br.readLine()) != null) {//使用readLine方法，一次读一行
                if (s.contains("任务启动时刻")) {
                    String[] startTimes = s.split("刻");
                    start_time = startTimes[1].trim().split(" ")[1] + " " + startTimes[1].trim().split(" ")[2];
                    System.out.println("任务启动时刻:-:" + start_time);
                }
                if (s.contains("任务结束时刻")) {
                    String[] startTimes = s.split("刻");
                    end_time = startTimes[1].trim().split(" ")[1] + " " + startTimes[1].trim().split(" ")[2];
                    System.out.println("任务结束时刻:-:" + end_time);
                }
                if (s.contains("任务总计耗时")) {
                    String[] startTimes = s.split("时");
                    seconds = Integer.parseInt(s.split(":")[1].trim().replace("s", "")
                    );
                    System.out.println("任务总计耗时:-:" + seconds);
                }
                if (s.contains("任务平均流量")) {
                    String[] startTimes = s.split("量");
                    traffic = s.split(":")[1].trim();
                    System.out.println("任务平均流量:-:" + traffic);
                }
                if (s.contains("记录写入速度")) {
                    String[] startTimes = s.split("度");
                    write_speed = s.split(":")[1].trim();
                    System.out.println("记录写入速度:-:" + write_speed);
                }
                if (s.contains("读出记录总数")) {
                    String[] startTimes = s.split("数");
                    read_record = Integer.parseInt(s.split(":")[1].trim());
                    System.out.println("读出记录总数:-:" + read_record);
                }
                if (s.contains("读写失败总数")) {
                    String[] startTimes = s.split("数");
                    failed_record = Integer.parseInt(s.split(":")[1].trim());
                    System.out.println("读写失败总数:-:" + failed_record);
                }
                // todo 不存在的情况
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void writeTo() {
        String sql = "insert into datax_job_result values(?,?,?,?,?,?,?,?,?,?,?,?)";//数据库操作语句（插入）

        Connection conn = getConnection();
        System.out.println("数据库连接成功");
        try {
            Statement statement = conn.createStatement();
            PreparedStatement pst = conn.prepareStatement(sql);//用来执行SQL语句查询，对sql语句进行预编译处理

            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            pst.setString(1, "nonono");
            pst.setString(2, "sdf");
//            Date startdate = dateFormat.parse(start_time);
//            pst.setTimestamp(3, Timestamp.valueOf(dateFormat.format(startdate.toString())));
            pst.setTimestamp(3, Timestamp.valueOf(start_time));
            pst.setString(4, end_time);
            pst.setInt(5, seconds);
            pst.setString(6, traffic);
            pst.setString(7, write_speed);
            pst.setInt(8, read_record);
            pst.setInt(9, failed_record);
            pst.setString(10, "");
            pst.setString(11, "");
//            Timestamp starttimestamp = new Timestamp(start_time);


            pst.setString(12, dateFormat.format(System.currentTimeMillis()));
            pst.executeUpdate();//解释在下
            System.out.println("---success---");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConnection(conn);
    }

    /**
     * 获取数据库下的所有表名
     */
    public static List<String> getTableNames() throws SQLException {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        Statement st = conn.createStatement();

        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            // oracle下获取表
//            db.getTables(null,"SYNONYM",null,new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
            int count = 1;
            for (String tableName : tableNames) {
                String table = tableName;
                System.out.println(tableName);
                String sql = "select count(*) from " + table;
                ResultSet resultSet = st.executeQuery(sql);
                while (resultSet.next()) {
                    count = resultSet.getInt(1);
                }
//                System.out.println(table + "总的数据量：" + count);
            }
        } catch (SQLException e) {
            LOGGER.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }


    //job_file
    //start_time
    //end_time
    //seconds
    //traffic
    //write_speed
    //read_record
    //failed_record
    //job_start
    //job_finish
    //insert_time

/*
    CREATE TABLE `datax_job_result` (
            `log_file` varchar(200) DEFAULT NULL,
  `job_file` varchar(200) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
            `end_time` datetime DEFAULT NULL,
            `seconds` int(11) DEFAULT NULL,
  `traffic` varchar(50) DEFAULT NULL,
  `write_speed` varchar(50) DEFAULT NULL,
  `read_record` int(11) DEFAULT NULL,
  `failed_record` int(11) DEFAULT NULL,
  `job_start` varchar(200) DEFAULT NULL,
  `job_finish` varchar(200) DEFAULT NULL,
  `insert_time` datetime

    alter TABLE datax_job_result MODIFY column insert_time TIMESTAMP default CURRENT_TIMESTAMP
*/


    public static void main(String[] args) throws SQLException {
        String logFileName = FindLatsetFile.getLogFile();
        readLog(logFileName);
//        readLog("E:\\workspace_directory\\Datax-web\\target\\datax\\datax\\log\\2020-04-14\\job\\mysql2mysql_json-18_33_59.470.log");
        writeTo();
//        getTableNames();
//        getConnection();
    }

}
